#!/usr/bin/python
import json , sys , os.path , time
from db import *
from logger import *
from faultsanalyzer import *

DEFAULT_SQL_RATE = 0.5
DEFAULT_FAULTS_MIN = 5

__author__ = 'meirdrago'

class SqlGroup(object):

        def __init__(self,name,db_conf,group_conf,compare_conf,logger,debug=False):
		self.name		= name
                self.group_conf         = group_conf
                self.compare_conf       = compare_conf
                self.db_conf       	= db_conf
		self.logger		= logger
		self.db			= None
		if debug 		: self.logger.setVerbose()

        #       TRIGGERED BY OUTSIDE
        def pulse(self):
		self.faults = {}
		if not self.initializeDb() : return
		sql = self.build_query()	
		data =  self.db.select_rows(sql)
		if len(data) == 0 :
			self.logger._log("groups.log" , "%s\tquery return 0 rows :\n%s" % (self.name , sql))	
			return
		self.mk_faults(data)
		if "monitor" in self.group_conf:
                	self.logger._log("monitor.log" , "%s\t%s" % (self.name , self.faults ))
	               	return
		self.analize()

	def mk_faults(self,data):
                for row in data:
			if row[0] == None : continue
                        val = {}
                        idx = 1
                        for k in self.compare_conf["rates"].keys() :
				if k not in self.group_conf['sumfields'] and k != "count" : continue 
                                rate = self.compare_conf["rates"][k] if k in self.compare_conf["rates"] else DEFAULT_SQL_RATE
                                if (float(self.compare_conf["rates"][k]) < 1.0 and row[idx] < float(self.compare_conf["rates"][k])) \
                                or \
                                (float(self.compare_conf["rates"][k]) > 1.0 and row[idx] > float(self.compare_conf["rates"][k])) :
                                        val[k] = 1.0
                                idx += 1
                        self.faults[row[0]] = val



        def analize(self):
                faultAnz = FaultsAnalyzer(self.group_conf , self.faults)
                faultAnz.do_process()
                for k in faultAnz.alertdata :
                        for ff in faultAnz.alertdata[k]:
                                if len(faultAnz.alertdata[k][ff].keys()) > 0:
                                        self.logger._log("results.log","%s\t%s\t%s" % (self.name , ff , ":".join(faultAnz.alertdata[k][ff].keys())))



	def build_query(self):
		query = """%s from ( select %s from %s where %s between '%s' and '%s' %s group by %s) master left join ( select %s from %s where %s between '%s' and '%s' %s group by %s ) slave
			on %s where master._count > %s and (cast(slave._count as float) / cast(master._count as float) < %s or slave._count is null %s )""" % \
			(self.get_selected_main() , \
			self.get_selected_fields() , self.db_conf["table"] , self.db_conf["time_field"] , self.db.times_ref[0] ,\
			self.db.times_ref[1] , self.get_filter_query() , ",".join(self.group_conf["fields"]) ,\
			self.get_selected_fields() , self.db_conf["table"] , self.db_conf["time_field"] , self.db.times_ref[2] ,\
			self.db.times_ref[3] , self.get_filter_query() , ",".join(self.group_conf["fields"]) ,\
			self.get_on_exp() , self.group_conf["minval"] , self.compare_conf['rates']['count'] , self.get_compare_expr())
		if self.db_conf["rdbms"] == "mysql" : query = query.replace("float","decimal(12,7)")
		return query

	def get_on_exp(self):
		return " and ".join(["master.%s = slave.%s" % (f,f) for f in self.group_conf["fields"]])

	def get_filter_query(self):
		if "filter" in self.group_conf and len(self.group_conf["filter"]) > 0:
			return " and " + " and ".join(["%s = '%s'" % (k,self.group_conf["filter"][k]) for k in self.group_conf["filter"] ])
		else : return ""


	def get_key(self):
		splitter = " + '%s' + " % (self.group_conf["splitter"])
		res      =  splitter.join(["master.%s" % (f) for f in self.group_conf["fields"]]) 
		if self.db_conf["rdbms"] == "mysql" :
			res = "concat(%s)" % (res.replace('+',','))
		return res + " k "
	
	def get_selected_main(self):
		select = "select %s , cast(slave._count as float) /  (cast(master._count as float) + 0.0001)" % (self.get_key())
		if len(self.group_conf["sumfields"].keys()) > 0 :
			fl = []
                        for k in self.group_conf["sumfields"].keys():
				fl.append("cast(slave.%s as float) /  (cast(master.%s as float) + 0.0001)" % (self.get_field_name(k),self.get_field_name(k)))
			select += " , " + " , ".join(fl)
		return select


	def get_selected_fields(self):
		field_alias = {"dist":"count(distinct ","sum":"sum(","avg":"avg("}
		select = "%s , count(*) _count " % (" , ".join(self.group_conf["fields"]))
		if len(self.group_conf["sumfields"].keys()) > 0 :
			fl = []
			for k in self.group_conf["sumfields"].keys():
				fl.append( "%s%s) %s" % (field_alias[self.group_conf["sumfields"][k]] , k , self.get_field_name(k)) )
			select += " , " + " , ".join(fl)
		return select

	#	if sumfields":{"uid":"dist","abb":"avg" , "asd":"sum"}
	#	then field names = [_duid , _aabb , _sasd]
	def get_field_name(self , field):
		return "_%s%s" % (self.group_conf["sumfields"][field][0:1] , field)


	def get_compare_expr(self):
		if not "sumfields" in self.group_conf or len(self.group_conf["sumfields"].keys()) == 0:
			return ""
		fl = []
		for k in self.group_conf["sumfields"].keys():
												#       0.0001 to avoid zero devision
			fl.append( "(master.%s > 0 and cast(slave.%s as float) / (cast(master.%s as float) + 0.0001) < %s)" % \
				(self.get_field_name(k) , self.get_field_name(k) , self.get_field_name(k) , self.compare_conf["rates"][k]))
		return " or " + " or ".join(fl)

	def initializeDb(self):
		self.db = DB(self.db_conf, self.logger)
		if self.db.connect() and self.db.time_ref():
			#      print self.db.ranges		
			#      print self.db.times_ref
			return True
		else:
        		self.logger._log("groups.log" , "%s\tDB Connection fail. sleeping ..." % (self.name))
			return True


#############################################################3

class SqlGroupManager(SqlGroup):
	group_conf 	= {}	
	compare_conf 	= {}	
	db_conf 	= {}	
	name		= None

	def __init__(self,conf_str ,debug=False ):
                self.logger = Logger("../logs")
                if debug:
                        self.logger.setVerbose()
                self.extract_params(conf_str )
		super(SqlGroupManager,self).__init__(self.name,self.db_conf,self.group_conf,self.compare_conf, self.logger,debug)	


        def extract_params(self , conf_str ):		
	        DB_FIELDS 	= ['seconds_offset','seconds_range','table','host','port','db','user','password','rdbms','time_field','fix_times' ]
		COMPARE_FIELDS =  ['faults_min','rates']					
		MANDATORY	= ['group','fields','table','host','port','db','user','password','minval']
                try:
                        conf = json.loads(conf_str)
                except :
                        print conf_str
                        self.logger._log("groups.log" , "Incorrect JSON. EXIT")
                        sys.exit(1)
		if "sql" in conf:
			for k in conf["sql"] : conf[k] = conf["sql"][k]
			del conf["sql"]
		for k in MANDATORY:
			if not k in conf:
				self.logger._log("groups.log" , "Missing Param %s\n%s" % (k,conf))
				sys.exit(1)
		for k in DB_FIELDS:
			if k in conf : self.db_conf[k] = conf[k]
		for k in COMPARE_FIELDS:
			if k in conf : self.compare_conf[k] = conf[k]
		for k in conf:
			if k not in self.db_conf and k not in self.compare_conf:
				self.group_conf[k] = conf[k]	
		self.name = conf["group"]

		#	OPTIONALS DEFUALTS
		if not 'rdbms' in self.db_conf : self.db_conf['rdbms'] = 'postgresql'
		if not 'time_field' in self.db_conf : self.db_conf['time_field'] = 'datetime'
		if not 'splitter' in self.group_conf : self.group_conf['splitter'] = '^^^'
		if not 'sumfields' in self.group_conf : self.group_conf['sumfields'] = {}
		if not 'faults_min' in self.compare_conf : self.compare_conf['faults_min'] = DEFAULT_FAULTS_MIN
		if not 'rates' in self.compare_conf :	
			print "TODO..."
